package com.zl_huang.example.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class GetDataFromExcel {
	
	
	public static void main(String[] args){
		String path = "D://Program Files//Apache Software Foundation//Tomcat 8.5//webapps//OnlineExam//Students.xlsx";
		Workbook workBook = getWorkbook(path);
		for(int numSheet = 0;numSheet < workBook.getNumberOfSheets();numSheet++){
			 Sheet sheet = workBook.getSheetAt(numSheet);
			 if(sheet == null){
				 continue;
			 }
			 for(int numRow = 1;numRow < sheet.getLastRowNum();numRow++){
				 Row row = sheet.getRow(numRow);
				 if(row != null){
					 String cellId = getValue(row.getCell(0));
					 String cellName = getValue(row.getCell(1));
					 String cellPassword = getValue(row.getCell(2));
					 String cellSex = getValue(row.getCell(3));
					 String cellEmail= getValue(row.getCell(4));
					 String cellBirthday = getValue(row.getCell(5));
					 String cellTel = getValue(row.getCell(6));
					 String cellAddress = getValue(row.getCell(7));
					 System.out.println(cellId+"          "+cellName+"          "+cellPassword+"          "+cellSex+"          "+cellEmail+"          "+cellBirthday+"          "+cellTel+"          "+cellAddress);
					 
					 
				 }else{
					 continue;
				 }
			 }
		}
	}
	public static Workbook getWorkbook(String fileName){
		Workbook workBook = null;		
		InputStream inputStream;
		try {
			inputStream = new FileInputStream(fileName);
			if(fileName.endsWith("xlsx") || fileName.endsWith("XLSX")){
				try {
					workBook = new XSSFWorkbook(inputStream);
				} catch (IOException e) {
					e.printStackTrace();
				}
			}else{
				try {
					workBook = new HSSFWorkbook(inputStream);
				} catch (IOException e) {					
					e.printStackTrace();
				}
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return workBook;
		
	}
	public static String getValue(Cell cell){
		if(cell == null){
			return "";			
		}else{
			if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
				return String.valueOf(cell.getBooleanCellValue());
			}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC ){
				return new DecimalFormat("0").format(cell.getNumericCellValue());
			}else{
				return String.valueOf(cell.getStringCellValue());
			}	
		}
		
	}
	
	
}
